JDBC-ODBC Tutorial ¶

Contents

JDBC-ODBC Tutorial

Introduction ¶

I wrote this tutorial because I found it very hard to find a decent one myself on the web whilst at University. I hope that this tutorial is enough to get anyone started who is familiar with the Java programming language and Microsoft Access databases. The tutorial aims to show you how you can create programs that rely on a Microsoft Access database.

Note

The screen shots for the tutorial were taken from a Windows XP Pro system.
Microsoft Access 97 and Microsoft Access 2000 databases have been tested with all sample code in this tutorial.
The examples were tested with Microsoft Visual Studio Service Pack 5 (updates Microsoft Access Data Components to version 2.5, including ODBC drivers)
Java 1.3 SDK Standard Edition

Getting Started ¶

You will need at least JDK 1.1 and of course, at least Microsoft Access 97 installed on your system.

I assume that you know how to use Access 97, so I will now assume that you already have a Microsoft Access database that you want to be able to access.

Microsoft uses ODBC (Open Database Connectivity) drivers with most of their software. Java uses JDBC (Java Database Connectivity) drivers to connect to various databases. JDBC cannot access an ODBC database directly, instead you have to use the JDBC-ODBC Bridge driver.

Before you can even touch Java, you need to tell the ODBC driver on your PC where your database is stored, so that it can create a data source that the JDBC-ODBC bridge driver can open.

For Windows 9x systems, go to Control Panel and open the “ODBC Data Sources (32bit)” applet.

For Windows XP and (I think) Windows 2000 systems go to Control Panel and open the “Data Sources (ODBC)” applet.

Note

Windows XP users: If the Data Sources applet is not visible in Control Panel then you can still access “Data Sources” by going to Control Panel> Administrative Tools> Data Sources (ODBC). Alternatively, you can make the applet visible in Control Panel, by downloading PowerToys for Windows XP from Microsoft’s Expert Zone. When you install the Power Toys, ensure you install “TweakUI”. Then fire up TweakUI, and go to Control Panel and tick “odbcc32.cpl”

Setting up ODBC ¶

Now that you have the ODBC control panel applet open, you should be presented with the following information:

Next you have to add a data source that the JDBC-ODBC Bridge driver can open.

To do so, click add.

The following dialog will now appear:

Make sure you select “Microsoft Access Driver” as shown above and click finish.

In the next screen enter a name for your data source - this is the name you will use in your Java code to open your database. Also enter a description. Use the “Select” button to browse for your database on your PC.

Below is what you should now have:

Click Ok. Your database will now be in the list of data sources:

Java ¶

Phew! Now all the ground work has been done, we can get down to the fun stuff. The following part of the tutorial shows you how to verify that you have done the above properly, and how to create a table in your database.

In your Java application, make sure you include the java.sql package.

Here is some sample code:

import java.sql.*;

public class Temp {
        public static void main(String args[]) {
                Connection con; // The connection to the database.
                // The following code can throw errors, so they must be caught.
                try {
                        // First, tell Java what driver to use and where to find it.
                        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                        // Next, create a connection to your data source.
                        // Specify that you are using the ODBC-JDBC Bridge.
                        // And specify the data source from ODBC.
                        con = DriverManager.getConnection("jdbc:odbc:Temp");
                        // Create an SQL statement.
                        Statement stmt = con.createStatement();
                        // Execute some SQL to create a table in your database.
                        // If the table already exists, an exception is thrown!
                        stmt.executeUpdate("CREATE TABLE COFFEES " +
                                "(COF_NAME VARCHAR(32), SUP_ID INTEGER, PRICE FLOAT, " +
                                "SALES INTEGER, TOTAL INTEGER)");
                }
                // Catch any exceptions that are thrown.
                catch(ClassNotFoundException e) {
                        System.out.println(e.toString());
                }
                catch(SQLException e) {
                        System.out.println(e.toString());
                }
        }
}

The following screen shot shows that the above code did infact create the table specified by the SQL:

Where next?¶

My tutorial is only to get you started. For further information on how to access databases, create relationships etc. please see the Java Tutorial by Sun.

JDBC-ODBC Tutorial¶

Introduction¶

Getting Started¶

Setting up ODBC¶

Java¶